#!/usr/bin/python
# -*- coding:utf-8 -*-
import MySQLdb
import tushare as ts
import time,os
import logging
"""
获取业绩报表披露日期，并且写入数据库
(只获取中国A股)
更新频率:每日
需要tushare_forgb 0.3.8
@author:kyan
@group:gubotech
@since:2015-10-15
"""

def _insert_disclose_date_into_db(stock_code, quarter, disclose_date):
    """
        db中写入业绩报告披露时间
    Parameters
    -------
    stock_code:string 股票代码 e.g:000001
    quarter:int 季度 e.g:1,2,3,4只能输入这4个季度
    disclose_date:string 披露日期 e.g:2015-10-14

    Return
    -------
    """
    fieldname = _mapping_quarter_to_fieldname(quarter)
    db = MySQLdb.connect(host='rdsqtehrv8tqh7v60yvujpublic.mysql.rds.aliyuncs.com',
                           user='tradingfloor',
                           passwd='tradingfloor123',
                           db='tradingreason',
                           charset='utf8')
    cursor = db.cursor()
    try:
        sql = "update stock_info set %s='%s' where stock_code = '%s' and asset_class = 'E'" % (fieldname, disclose_date, stock_code)
        cursor.execute(sql)
        db.commit()
    except:
        logger.error("error while inserting stock %s" % stock_code)
        db.rollback()
    db.close()

def _mapping_quarter_to_fieldname(quarter):
    """
        映射quarter和db字段名称
    Parameters
    -------
    quarter:int 季度 e.g:1,2,3,4只能输入这4个季度

    Return
    -------
    db中的对应字段名
    """
    return {
        1:'disclose_date_q1',
        2:'disclose_date_q2',
        3:'disclose_date_q3',
        4:'disclose_date_q4'
        }[quarter]

def _previous_quarter(quarter):
    if quarter > 1:
        return quarter-1
    else:
        return 4


# logger config
logger = logging.getLogger(__name__)
logger.setLevel(logging.INFO)
log_path = '/tmp/log/report_disclose_date_crawler_log'
if not os.path.exists(log_path):
    os.makedirs(log_path)
handler = logging.FileHandler(log_path+'/%s.log' % time.strftime("%Y-%m-%d"))
handler.setLevel(logging.INFO)
formatter = logging.Formatter('%(asctime)s %(levelname)s %(message)s')
handler.setFormatter(formatter)
logger.addHandler(handler)

if __name__ == '__main__':

    logger.info('------  %s ------' % time.strftime("%Y-%m-%d  %H:%M:%S"))
    year = int(time.strftime("%Y"))
    month = int(time.strftime("%m"))
    curr_quarter = (month-1)//3 + 1
    pre_quarter = _previous_quarter(curr_quarter)

    # read report disclose date here
    logger.info('Start reading report disclose date...')
    df = ts.get_report_disclose_date(year, pre_quarter)

    # update report disclose date here
    logger.info('Start updating report disclose date in db...')
    for i in range(len(df.index)):
        code = df.iloc[i]['code']
        if df.iloc[i]['final_date']:
            logger.info('%s insert disclose date for quarter %s : %s' % (code, pre_quarter, df.iloc[i]['final_date']))
            _insert_disclose_date_into_db(code, pre_quarter, df.iloc[i]['final_date'])
            continue
        elif df.iloc[i]['third_change']:
            logger.info('%s insert disclose date for quarter %s : %s' % (code, pre_quarter, df.iloc[i]['third_change']))
            _insert_disclose_date_into_db(code, pre_quarter, df.iloc[i]['third_change'])
            continue
        elif df.iloc[i]['second_change']:
            logger.info('%s insert disclose date for quarter %s : %s' % (code, pre_quarter, df.iloc[i]['second_change']))
            _insert_disclose_date_into_db(code, pre_quarter, df.iloc[i]['second_change'])
            continue
        elif df.iloc[i]['first_change']:
            logger.info('%s insert disclose date for quarter %s : %s' % (code, pre_quarter, df.iloc[i]['first_change']))
            _insert_disclose_date_into_db(code, pre_quarter, df.iloc[i]['first_change'])
            continue
        else:
            logger.info('%s insert disclose date for quarter %s : %s' % (code, pre_quarter, df.iloc[i]['expected_date']))
            _insert_disclose_date_into_db(code, pre_quarter, df.iloc[i]['expected_date'])
            continue
    logger.info('End: %s stocks have been updated' % len(df.index))
